package edu.hhu.dynamic.table;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import com.alibaba.fastjson.JSON;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

public class TableDao {
	// 单井（不归一、时间归一）
	public String getData(Conditions conditions) {
		String sqltemple = getTableSql(conditions);
		String sql = getFy(sqltemple, conditions.getStart(), conditions.getLimit());
		String total = getTotalSql(sqltemple);
		List<Record> data = Db.use("mysql").find(sql);
		List jsonList = new ArrayList();
		int i = 0;
		for (Record record : data) {
			Map map = new HashMap<String, String>();
			map = record.getColumns();
			ToZero(map);
			jsonList.add(map);
			i++;
		}
		String json = JSON.toJSONString(jsonList);
		json = "{\"total\":" + total + ",\"items\":" + json + "}";
		return json;
	}

	// 单井产量归一、全归一
	public String getData1(Conditions conditions) {
		String sqltemple = getTableSql1(conditions);
		String sql = getFy(sqltemple, conditions.getStart(), conditions.getLimit());
		String total = getTotalSql1(conditions);
		List<Record> data = Db.use("mysql").find(sql);
		List jsonList = new ArrayList();
		int i = 0;
		for (Record record : data) {
			Map map = new HashMap<String, String>();
			map = record.getColumns();
			ToZero(map);
			jsonList.add(map);
			i++;
		}
		String json = JSON.toJSONString(jsonList);
		json = "{\"total\":" + total + ",\"items\":" + json + "}";
		return json;
	}

	// 井组、区块、气田（不归一、时间归一）
	public String getData2(Conditions conditions) {
		String sqltemple = getTableSql2(conditions);
		String sql = getFy(sqltemple, conditions.getStart(), conditions.getLimit());
		String total = getTotalSql(sqltemple);
		List<Record> data = Db.use("mysql").find(sql);
		List jsonList = new ArrayList();
		int i = 0;
		for (Record record : data) {
			Map map = new HashMap<String, String>();
			map = record.getColumns();
			ToZero(map);
			jsonList.add(map);
			i++;
		}
		String json = JSON.toJSONString(jsonList);
		json = "{\"total\":" + total + ",\"items\":" + json + "}";
		return json;
	}

	// null转化为0
	public void ToZero(Map map) {
		Set<Map.Entry> set = map.entrySet();
		for (Entry entry : set) {
			String key = (String) entry.getKey();
			if (entry.getValue() == null) {
				map.put(key, "0");
			}
		}
	}

	// 获取总的条数
	public String getTotalSql(String sql) {
		sql = "select count(*) total from (" + sql + ") as tt;";
		List<Record> data = Db.use("mysql").find(sql);
		String total = "";
		for (Record record : data) {
			total = record.getColumns().get("total").toString();
		}
		return total;
	}
	// 获取：单井（产量归一、全归一）总的条数
	public String getTotalSql1(Conditions conditions) {
		String sql = "select count(*) total from " + conditions.getTablename() 
		             +" where wellcode='"+conditions.getNodeStr()+"';";
		List<Record> data = Db.use("mysql").find(sql);
		String total = "";
		for (Record record : data) {
			total = record.getColumns().get("total").toString();
		}
		return total;
	}
	// 分页、返回：sql语句
	public String getFy(String sql, String start, String limit) {
		sql = "select * from (" + sql + " order by SCRQ " + ") as tt limit " + start + "," + limit + ";";
		return sql;
	}


	// 返回：单井（不归一、时间归一）sql语句
	public String getTableSql(Conditions conditions) {
		String sql = "";
		// 井号信息
		String nodesCon = " WELLCODE='" + conditions.getNodeStr() + "' ";
		// 井属性信息
		String fieldConn = conditions.getFieldStr();

		sql = "SELECT WELLNAME,DATE_FORMAT(SCRQ,'%Y-%m-%d') SCRQ," + fieldConn + " FROM " + conditions.getTablename()
				+ "  WHERE SCRQ BETWEEN '" + conditions.getS_date() + "' AND '" + conditions.getE_date() + "' and "
				+ nodesCon;
		return sql;
	}

	// 返回：单井（产量归一、全归一）sql语句
	public String getTableSql1(Conditions conditions) {
		String table = "SELECT WELLCODE,WELLNAME,SCRQ,QQ,QL,SCSJ  FROM " + conditions.getTablename()
				+ " t  WHERE  WELLCODE='" + conditions.getNodeStr() + "'";
		
		// 井属性信息
		String fieldConn = conditions.getFieldStr();

		String sql = "SELECT WELLNAME,DATE_FORMAT(SCRQ,'%Y-%m-%d') SCRQ," + fieldConn + " FROM " 
		        + "(SELECT wellcode,wellname,SCRQ,QQ,QL FROM (" +table+ ") t WHERE !(SCSJ>0 AND SCSJ<24) || SCSJ IS NULL" 
		        + " UNION "
				+ "SELECT wellcode,wellname,SCRQ,QQ*(24/SCSJ) QQ, QL*(24/SCSJ) QL FROM (" + table+ ") t WHERE  SCSJ>0 AND SCSJ<24) tt";
		return sql;
	}
	// 返回：井组、区块、气田（不归一）sql语句
	public String getTableSql2(Conditions conditions) {
		String sql = "";
		String level=conditions.getLevel();
		// 井号信息
		String nodesCon="";
		String Name="";
		if(level.equals("2")){
			nodesCon = " wellgroupcode='" + conditions.getNodeStr() + "' ";
			Name = "wellgroupname";
		}else if(level.equals("1")){
			nodesCon = " blockcode='" + conditions.getNodeStr() + "' ";
			Name = "blockname";
		}else if(level.equals("0")){
			nodesCon = " gasfieldcode='" + conditions.getNodeStr() + "' ";
			Name = "gasfieldname";
		}
		
		
		// 井属性信息
		String fieldConn = conditions.getFieldStr();

		sql = "SELECT "+Name+" WELLNAME,DATE_FORMAT(SCRQ,'%Y-%m-%d') SCRQ," + fieldConn + " FROM " + conditions.getTablename()
				+ "  WHERE SCRQ BETWEEN '" + conditions.getS_date() + "' AND '" + conditions.getE_date() + "' and "
				+ nodesCon;
		return sql;
	}

	//  返回：数据导出sql语句
	public String getExcelSql(Conditions conditions) {
		String sql = "";
		//是否归一
		String level=conditions.getLevel();
		String normalType=conditions.getNormalType();
		if(level.equals("3")){
			//单井不归一
			if(normalType.equals("0")||normalType.equals("1")){
				// 井号信息
				String[] nodes = conditions.getNodeStr().split(",");
				String nodesCon = "(";
				for (int i = 0; i < nodes.length; i++) {
					nodesCon += " WELLCODE='" + nodes[i] + "' or";
				}
				nodesCon = nodesCon.substring(0, nodesCon.length() - 3);
				nodesCon = nodesCon + ")";
				// 井属性信息
				String fieldConn = conditions.getFieldStr();

				sql = "SELECT WELLCODE,WELLNAME,DATE_FORMAT(SCRQ,'%Y-%m-%d') SCRQ," + fieldConn + " FROM " + conditions.getTablename()
						+ "  WHERE SCRQ BETWEEN '" + conditions.getS_date() + "' AND '" + conditions.getE_date() + "' and " + nodesCon + ";";
				return sql;
			}else{
				//单井归一
				
				// 井号信息
				String[] nodes = conditions.getNodeStr().split(",");
				String nodesCon = "(";
				for (int i = 0; i < nodes.length; i++) {
					nodesCon += " WELLCODE='" + nodes[i] + "' or";
				}
				nodesCon = nodesCon.substring(0, nodesCon.length() - 3);
				nodesCon = nodesCon + ")";
				
				String table = "SELECT WELLCODE,WELLNAME,SCRQ,QQ,QL,SCSJ  FROM " + conditions.getTablename()
				+ " t  WHERE  " +nodesCon;
		
		        // 井属性信息
		        String fieldConn = conditions.getFieldStr();

		       sql = "SELECT WELLCODE,WELLNAME,DATE_FORMAT(SCRQ,'%Y-%m-%d') SCRQ," + fieldConn + " FROM " 
		        + "(SELECT wellcode,wellname,SCRQ,QQ,QL FROM (" +table+ ") t WHERE !(SCSJ>0 AND SCSJ<24) || SCSJ IS NULL" 
		        + " UNION "
				+ "SELECT wellcode,wellname,SCRQ,QQ*(24/SCSJ) QQ, QL*(24/SCSJ) QL FROM (" + table+ ") t WHERE  SCSJ>0 AND SCSJ<24) tt";
		        return sql;
			}
		}else{
			if(level.equals("2")){
				// 井号信息
				String[] nodes = conditions.getNodeStr().split(",");
				String nodesCon = "(";
				for (int i = 0; i < nodes.length; i++) {
					nodesCon += " wellgroupcode='" + nodes[i] + "' or";
				}
				nodesCon = nodesCon.substring(0, nodesCon.length() - 3);
				nodesCon = nodesCon + ")";
				// 井属性信息
				String fieldConn = conditions.getFieldStr();

				sql = "SELECT wellgroupcode WELLCODE,wellgroupname WELLNAME,DATE_FORMAT(SCRQ,'%Y-%m-%d') SCRQ," + fieldConn + " FROM " + conditions.getTablename()
						+ "  WHERE SCRQ BETWEEN '" + conditions.getS_date() + "' AND '" + conditions.getE_date() + "' and " + nodesCon + ";";
				return sql;
			}
			if(level.equals("1")){
				// 井号信息
				String[] nodes = conditions.getNodeStr().split(",");
				String nodesCon = "(";
				for (int i = 0; i < nodes.length; i++) {
					nodesCon += " blockcode='" + nodes[i] + "' or";
				}
				nodesCon = nodesCon.substring(0, nodesCon.length() - 3);
				nodesCon = nodesCon + ")";
				// 井属性信息
				String fieldConn = conditions.getFieldStr();

				sql = "SELECT blockcode WELLCODE,blockname WELLNAME,DATE_FORMAT(SCRQ,'%Y-%m-%d') SCRQ," + fieldConn + " FROM " + conditions.getTablename()
						+ "  WHERE SCRQ BETWEEN '" + conditions.getS_date() + "' AND '" + conditions.getE_date() + "' and " + nodesCon + ";";
				return sql;
			}
			if(level.equals("0")){
				// 井号信息
				String[] nodes = conditions.getNodeStr().split(",");
				String nodesCon = "(";
				for (int i = 0; i < nodes.length; i++) {
					nodesCon += " gasfieldcode='" + nodes[i] + "' or";
				}
				nodesCon = nodesCon.substring(0, nodesCon.length() - 3);
				nodesCon = nodesCon + ")";
				// 井属性信息
				String fieldConn = conditions.getFieldStr();

				sql = "SELECT gasfieldcode WELLCODE,gasfieldname WELLNAME,DATE_FORMAT(SCRQ,'%Y-%m-%d') SCRQ," + fieldConn + " FROM " + conditions.getTablename()
						+ "  WHERE SCRQ BETWEEN '" + conditions.getS_date() + "' AND '" + conditions.getE_date() + "' and " + nodesCon + ";";
				return sql;
			}
		}
		
		
		return "";
	}

	public List getHaeder() {
		String sql = "select fieldcode,concat(fieldname,'（',unitname,'）') fieldname from dictionary ;";
		List<Record> data = Db.use("mysql").find(sql);
		List jsonList = new ArrayList();
		int i = 0;
		for (Record record : data) {
			jsonList.add(record.getColumns());
			i++;
		}
		String json = JSON.toJSONString(jsonList);
		return jsonList;
	}

}
